﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using MySql.Data.MySqlClient;

namespace DbFrame.AdoDotNet
{
    public class MySqlDatabase : IDataBase
    {

        private string _ConnectionString { get; set; }

        public MySqlDatabase(string ConnectionString)
        {
            this._ConnectionString = ConnectionString;
        }

        public bool Commit(List<Class.SQL> li)
        {
            return this.Commit(li, (i, item, cmd) =>
            {
                //执行sql
                cmd.CommandText = item.Sql_Parameter;
                foreach (var par in item.Parameter)
                {
                    cmd.Parameters.Add(new MySqlParameter() { ParameterName = par.Key, Value = par.Value == null ? DBNull.Value : par.Value });
                }
                cmd.ExecuteNonQuery();
            });
        }

        public bool Commit(List<Class.SQL> li, Action<int, Class.SQL, System.Data.Common.DbCommand> callBack)
        {
            using (var conn = new MySqlConnection(_ConnectionString))
            {
                using (var cmd = new MySqlCommand())
                {
                    conn.Open();
                    var tx = conn.BeginTransaction();
                    cmd.Connection = conn;
                    cmd.Transaction = tx;
                    try
                    {
                        li.ForEach(item =>
                        {
                            cmd.Parameters.Clear();
                            callBack(li.IndexOf(item), item, cmd);
                        });
                        //提交事务
                        tx.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        //失败则回滚事务
                        tx.Rollback();
                        throw ex;
                    }
                    finally
                    {
                        conn.Close();
                    }

                }

            }
        }

        /// <summary>
        /// 执行 增/删/改
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string strSql)
        {
            return this.ExecuteNonQuery(strSql, null);
        }

        /// <summary>
        /// 执行 增/删/改 参数化
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="dbParameter"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string strSql, params System.Data.Common.DbParameter[] dbParameter)
        {
            using (var connection = new MySqlConnection(_ConnectionString))
            {
                using (var cmd = new MySqlCommand())
                {
                    PrepareCommand(cmd, connection, null, strSql, dbParameter);
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
            }
        }

        /// <summary>
        /// 执行 查询
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataSet ExecuteDataset(string strSql)
        {
            return this.ExecuteDataset(strSql, null);
        }

        /// <summary>
        /// 执行 查询 参数化
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="dbParameter"></param>
        /// <returns></returns>
        public DataSet ExecuteDataset(string strSql, params System.Data.Common.DbParameter[] dbParameter)
        {
            using (var connection = new MySqlConnection(_ConnectionString))
            {
                using (var cmd = new MySqlCommand())
                {
                    PrepareCommand(cmd, connection, null, strSql, dbParameter);
                    var dr = cmd.ExecuteReader();

                    using (var da = new MySqlDataAdapter(cmd))
                    {
                        var ds = new DataSet();
                        da.Fill(ds);
                        cmd.Parameters.Clear();
                        return ds;
                    }
                }
            }
        }

        /// <summary>
        /// 获取 DataTable
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string strSql)
        {
            var tabs = this.ExecuteDataset(strSql).Tables;
            if (tabs.Count > 0)
                return tabs[0];
            return null;
        }
        /// <summary>
        /// 获取 DataTable
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="dbParameter"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string strSql, params DbParameter[] dbParameter)
        {
            var tabs = this.ExecuteDataset(strSql, dbParameter).Tables;
            if (tabs.Count > 0)
                return tabs[0];
            return null;
        }

        public int ExecuteByProc(string procName)
        {
            using (var connection = new MySqlConnection(_ConnectionString))
            {

            }
            throw new NotImplementedException();
        }

        public int ExecuteByProc(string procName, System.Data.Common.DbParameter[] dbParameter)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 查询 返回第一行第一列
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public object ExecuteScalar(string strSql)
        {
            return this.ExecuteScalar(strSql, null);
        }

        /// <summary>
        /// 查询 返回第一行第一列
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="dbParameter"></param>
        /// <returns></returns>
        public object ExecuteScalar(string strSql, System.Data.Common.DbParameter[] dbParameter)
        {
            using (var connection = new MySqlConnection(_ConnectionString))
            {
                using (var cmd = new MySqlCommand())
                {
                    PrepareCommand(cmd, connection, null, strSql, dbParameter);
                    var rel = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return rel;
                }
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="strSql">sql 语句</param>
        /// <param name="dbParameter">参数化</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="isAsc">是否正序</param>
        /// <param name="pageSize">一页显示多少条</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="total">总数</param>
        /// <returns></returns>
        public DataTable FindTable(string strSql, DbParameter[] dbParameter, string orderField, bool isAsc, int pageSize, int pageIndex, out int total)
        {
            StringBuilder sb = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            string OrderBy = "";

            if (!string.IsNullOrEmpty(orderField))
            {
                if (orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC") > 0)
                {
                    OrderBy = "Order By " + orderField;
                }
                else
                {
                    OrderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
                }
            }
            else
            {
                OrderBy = "order by (select 0 )";
            }
            sb.Append(strSql + OrderBy);
            sb.Append(" limit " + num + "," + pageSize + "");
            total = Convert.ToInt32(this.ExecuteScalar("Select Count(1) From (" + strSql + ") As t", dbParameter));
            var dt = this.ExecuteDataTable(sb.ToString(), dbParameter);
            return dt;
        }

        /// <summary>
        /// 执行ADO.NET SQL时要用的参数添加
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms) cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// 执行SQL存储过程时要用的参数添加
        /// <returns>SqlDataReader</returns>
        /// 
        private static void PrepareCommand(DbCommand cmd, MySqlTransaction trans, IDataParameter[] cmdParms)
        {
            if (trans != null)
                cmd.Transaction = trans;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms) cmd.Parameters.Add(parm);
            }
        }











    }
}
